<?php

/*
 * Copyright (C) 2006 - 2010 Pham Cong Dinh
 *
 * This file is part of Spica.
 *
 * This is free software; you can redistribute it and/or modify it
 * under the terms of the GNU Lesser General Public License as
 * published by the Free Software Foundation; either version 3 of
 * the License, or (at your option) any later version.
 *
 * This software is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this software; if not, write to the Free
 * Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
 * 02110-1301 USA, or see the FSF site: http://www.fsf.org.
*/

/**
 * The object used for executing a static SQL statement and returning the results it produces
 *
 * By default, only one <code>SpicaMySQLResultSet</code> object
 * per <code>SpicaMySQLStatement</code> object can be open at the same time
 *
 * Therefore, if the reading of one <code>SpicaMySQLResultSet</code> object
 * is interleaved with the reading of another, each must have been generated
 * by different <code>SpicaMySQLStatement</code> objects
 *
 * All execution methods in the <code>SpicaMySQLStatement</code> interface
 * implicitly close a statment's current <code>SpicaMySQLResultSet</code>
 * object if an open one exists
 *
 * @category   spica
 * @package    core
 * @subpackage datasource\db\mysql
 * @author     Pham Cong Dinh <pcdinh at phpvietnam dot net>
 * @since      Version 0.1
 * @since      October 18, 2008
 * @copyright  Pham Cong Dinh (http://www.phpvietnam.net)
 * @license    http://www.gnu.org/licenses/lgpl-3.0.txt
 * @version    $Id: Statement.php 1867 2010-06-07 04:40:51Z pcdinh $
 */

/**
 * Static statement interface
 */
require_once 'library/spica/core/datasource/db/Statement.php';

/**
 * Result set interface
 */
require_once 'library/spica/core/datasource/db/ResultSet.php';

class SpicaMySQLStatement extends SpicaMySQLCommonStatement implements SpicaStatement
{
    /**
     * Database connection
     *
     * @var SpicaMySQLConnection
     */
    protected $_dbConn;

    /**
     * An array of queries in a batch
     *
     * @var array
     */
    protected $_batchQueries = array();

    /**
     * An array of results after batch execution
     *
     * @var array
     */
    protected $_batchResults = array();

    /**
     * The current offset of the query being executed
     *
     * @var int
     */
    protected $_previousBatchResultOffset = -1;

    /**
     * Current result set
     *
     * @var SpicaMySQLResultSet
     */
    protected $_currentResultSet;

    /**
     * Flag to indicate that a query is a kind of SELECT or not
     *
     * @var bool
     */
    protected $_isSelect;

    /**
     * Has this connection been closed?
     *
     * @var bool
     */
    protected $_isClosed = false;

    /**
     * The last query is executed
     *
     * @var string
     */
    protected $_lastQuery;

    /**
     * Constructs an object of <code>SpicaMySQLStatement</code>
     *
     * @param $dbConn SpicaMySQLConnection
     */
    public function __construct($dbConn)
    {
        $this->_dbConn = $dbConn;
    }

    /**
     * Cancels this statement object if both the DBMS and driver support aborting an SQL statement
     *
     * @throws SpicaDatabaseException
     */
    public function cancel()
    {
        if (false === $this->_isClosed && null !== $this->_dbConn)
        {
            try
            {
                $conn = $this->getNativeConnection();
            }
            catch (SpicaDatabaseException $ex)
            {
                throw new SpicaDatabaseException('Unable to cancel the statement
                because database connection has been closed. ', null, null, null, $ex);
            }

            $id     = mysqli_thread_id($conn);
            $result = mysqli_query($conn, 'KILL QUERY '.$id);

            if (false === $result)
            {
                throw new SpicaDatabaseException('Unable to cancel the statement attached with thread ID '.$id, mysqli_error($conn), mysqli_sqlstate($conn), mysqli_errno($conn));
            }

            return true;
        }
    }

    /**
     * Gives the Spica database driver a hint as to the number of rows that should be fetched
     * from the database when more rows are needed for this SpicaResultSet object.
     *
     * @see http://forums.mysql.com/read.php?39,137457,137457#msg-137457
     * @see http://bugs.mysql.com/bug.php?id=18148
     * @param int $rows
     */
    public function setFetchSize($rows)
    {
        // no-op
    }

    /**
     * Adds the given SQL command to the current list of commmands for this Statement object
     *
     * @var string $sql
     */
    public function addBatch($sql)
    {
        $this->_guessQueryType($sql);

        if (false === $this->_isSelect)
        {
            $this->_checkUpdatable();
        }

        $this->_batchQueries[] = $sql;
    }

    /**
     * Prepares a SQL statement for execution.
     *
     * The following statements can be used as prepared statements:
     *  + CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, and most SHOW statements.
     *  + Other statements are not supported in MySQL 5.0
     *
     * @see    http://bugs.php.net/bug.php?id=40852
     * @see    SpicaMySQLConnection#prepareStatement()
     * @throws SpicaDatabaseException when database connection is closed, read-only or SQL command execution fails
     * @param  string $sql
     * @return SpicaMySQLPreparedStatement
     */
    public function prepare($sql)
    {
        $this->_guessQueryType($sql);

        if (false === $this->_isSelect)
        {
            $this->_checkUpdatable();
        }

        try
        {
            $conn = $this->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to create a prepared statement because database connection has been closed. ', null, null, null, $ex);
        }

        require_once 'spica/core/datasource/db/mysql/PreparedStatement.php';
        return new SpicaMySQLPreparedStatement($conn, $sql);
    }

    /**
     * Executes a query.
     *
     * @see    SpicaStatement#execute($sql)
     * @throws SpicaDatabaseException when database connection is closed, read-only or SQL command execution fails
     * @return bool
     */
    public function execute($sql)
    {
        // No trailing and/or leading whitespace please
        $this->_lastQuery = trim($sql);

        $this->_guessQueryType();

        if (true === $this->_isSelect)
        {
            return $this->_doRealQueryExecution($this->_lastQuery);
        }
        else
        {
            $this->_checkUpdatable();
            return $this->_doRealQueryExecution($sql);
        }
    }

    /**
     * Executes a query that may cause lock wait timeout or deadlock in a transactions
     *
     * 1205 (ER_LOCK_WAIT_TIMEOUT)
     * Lock wait timeout expired. Transaction was rolled back.
     * 1213 (ER_LOCK_DEADLOCK)
     * Transaction deadlock. You should rerun the transaction
     * @throws SpicaDatabaseException when database connection is closed, read-only or SQL command execution fails
     * @param  string $sql
     */
    public function executeWithDeadlockAware($sql)
    {
        // Check closed statement
        $this->_checkClosed();

        $maxAttempts = 100;
        $current     = 0;

        try
        {
            $conn = $this->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute the deadlock-aware SQL command because database connection has been closed. ', null, null, null, $ex);
        }

        while ($current++ < $maxAttempts)
        {
            $result   = mysqli_query($conn, $sql);
            $code     = mysqli_errno($conn);

            if (false === $result && (1205 ===  $code || 1213 === $code))
            {
                continue;
            }

            throw new SpicaDatabaseException('Unable to execute the deadlock-aware SQL command. ',
            mysqli_error($conn), mysqli_sqlstate($conn), $code);
        }
    }

    /**
     * Executes a batch of queries
     *
     * SpicaMySQLStatement just ends up sending a bunch of queries one-at-a-time
     *
     * We use a feature that is supported by MySQL 4.1.x+ to rewrite batch into a single query to send to the server
     * This method will constrain each batch to be shorter than max_allowed_packet on the server
     *
     * When using mysqli_multi_query you always have to process the result sets before sending a new statement to the server
     *
     * Even if there is no result set (for non SELECT/SHOW/DESCRIBE statements) you have to do that
     * to obtain error codes for the single statements
     *
     * If an error occurs within a batch the all SQL commands executed to that point remain valid
     * while all further commands will be rejected (quoted from The Definitive Guide to MySQL 5 - page 523)
     * This behaviour is similar to one in PostgreSQL 8 (@see http://archives.postgresql.org/pgsql-jdbc/2007-01/msg00094.php)
     *
     * @throws SpicaDatabaseException when database connection is closed, read-only or SQL command execution fails
     * @see    SpicaStatement#addBatch()
     * @see    SpicaStatement#executeBatch()
     * @return array An array of batch results
     */
    public function executeBatch()
    {
        // Check closed statement
        $this->_checkClosed();

        // Returns immediately if the batch is empty
        if (0 === ($totalCommand = count($this->_batchQueries)))
        {
            return array();
        }

        try
        {
            $conn = $this->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute the SQL batch command because database connection has been closed. ', null, null, null, $ex);
        }

        $query   = implode(';', $this->_batchQueries);

        // Makes the first query and get first answer result
        // Returns FALSE if the first statement failed
        $success = mysqli_multi_query($conn, $query);

        // Result of batch query execution
        $rs      = array();
        // SQL command order count
        $count   = 0;

        // Make it inline for performance (see switch statement below)
        $caseMode      = $this->_dbConn->getSQLIdentifierCase();
        $lowerCaseMode = SpicaResultSet::IDENTIFIER_LOWERCASE;
        $upperCaseMode = SpicaResultSet::IDENTIFIER_UPPERCASE;

        /**
         * @see http://dev.mysql.com/doc/refman/5.0/en/mysql-next-result.html
         * @see http://svn.mysql.com/svnpublic/php-mysqlnd/trunk/php5/ext/mysqli/mysqli_api.c
         * @see http://svn.mysql.com/svnpublic/php-mysqlnd/trunk/mysqlnd/mysqlnd.c
         * @see http://bugs.php.net/bug.php?id=46448
         */
        while (mysqli_more_results($conn))
        {
            mysqli_next_result($conn);

            // store first result set into a <code>mysqli_result</code> object (transfers a result set from the last query)
            // process it before sending another statement to the server
            // otherwise the socket is still blocked
            // mysqli_store_result() returns FALSE in case the query didn't return a result set (if the query was, for example an INSERT statement)
            $result   = mysqli_store_result($conn);

            if (false === $result)
            {
                // Error occurs
                if (mysqli_errno($conn) > 0)
                {
                    // Error occurs
                    break;
                }

                // We were simply executing a query that returns no result set (INSERT, DELETE, UPDATE, TRUNCATE)
                $rs[$count] = array(
                    // Returns the number of rows affected by the last INSERT, UPDATE, REPLACE or DELETE query
                    // An integer greater than zero indicates the number of rows affected or retrieved.
                    // 0 indicates that no records where updated for an UPDATE statement, no rows matched the WHERE clause in the query or that no query has yet been executed.
                    // -1 indicates that the query returned an error
                    'affected_rows'  => mysqli_affected_rows($conn),
                    // The value of the AUTO_INCREMENT field that was updated by the previous query.
                    // Returns zero if there was no previous query on the connection or if the query did not update an AUTO_INCREMENT value
                    'last_insert_id' => mysqli_insert_id($conn), // 0 (no ID) or bigger (last insert ID)
                    // No result set
                    'no_resultset'   => true
                );
            }
            else
            {
                while ($row = mysqli_fetch_assoc($result))
                {
                    switch ($caseMode)
                    {
                        case $lowerCaseMode:
                            $row = array_change_key_case($row, CASE_LOWER);
                            break;

                        case $upperCaseMode:
                            $row = array_change_key_case($row, CASE_UPPER);
                            break;

                        default:
                        // no-op (present here for coding standard compliant)
                    }

                    $rs[$count]  = $row;
                }

                mysqli_free_result($result);
            }

            $count++;
        }

        if (mysqli_errno($conn) > 0)
        {
            // SpicaDatabaseBatchUpdateException is defined in ConnectionFactory.php
            throw new SpicaDatabaseBatchUpdateException('Unable to execute the SQL command indexed '.$count.' in total of '.$totalCommand.' commands', mysqli_error($conn), mysqli_sqlstate($conn), mysqli_errno($conn));
        }

        return $rs;
    }

    /**
     * Executes the given SQL statement, which returns a single MySQLResultSet object
     * like SELECT, SHOW, DESCRIBE or EXPLAIN
     *
     * @see    SpicaStatement#executeQuery
     * @param  string $sql an SQL statement to be sent to the database, typically a static SQL <code>SELECT</code> statement
     * @return SpicaMySQLResultSet
     */
    public function executeQuery($sql)
    {
        $this->_guessQueryType();

        if (false === $this->_isSelect)
        {
            throw new SpicaDatabaseException('Wrong use of Statement#executeQuery(). This method is used for SELECT, SHOW, DESCRIBE or EXPLAIN only. ');
        }

        $this->_doRealQueryExecution($sql);
        return $this->getResultSet();
    }

    /**
     * Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement
     * or an SQL statement that returns nothing, such as an SQL DDL statement
     *
     * @throws SpicaDatabaseException when database connection is closed, read-only or SQL command execution fails
     * @see    SpicaStatement#executeUpdate($sql)
     * @param string $sql an SQL Data Manipulation Language (DML) statement, such as <code>INSERT</code>,
     *                    <code>UPDATE</code> or <code>DELETE</code>; or an SQL statement that
     *                    returns nothing, such as a DDL statement
     * @return bool
     */
    public function executeUpdate($sql)
    {
        if (true === $this->_dbConn->isReadOnly())
        {
            throw new SpicaDatabaseException('Connection is read-only. Queries leading to data modification are not allowed. ');
        }

        $this->_isSelect = false;
        return $this->_doRealQueryExecution($sql);
    }

    /**
     * Executes a SQL against database server
     *
     * @param  string $sql
     * @return bool
     */
    protected function _doRealQueryExecution($sql)
    {
        if (false === is_string($sql))
        {
            throw new SpicaDatabaseException('Invalid SQL command. ');
        }

        // Check closed statement
        $this->_checkClosed();

        try
        {
            $conn = $this->_dbConn->getNativeConnection(); // establish connection
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute the SQL command because database connection has been closed. ', null, null, null, $ex);
        }

        // For SELECT, SHOW, DESCRIBE or EXPLAIN mysqli_query() will return a result object.
        // Otherwise, it returns TRUE on success or FALSE on failure
        $result = mysqli_query($conn, $sql);

        if ($result instanceof mysqli_result)
        {
            $this->_currentResultSet = new SpicaMySQLResultSet($result, true, $this->_dbConn->getSQLIdentifierCase());
            return true;
        }
        else
        {
            if (false === $result)
            {
                throw new SpicaDatabaseException('Could not execute SQL command. ', mysqli_error($conn), mysqli_sqlstate($conn), mysqli_errno($conn));
            }

            $this->_currentResultSet = new SpicaMySQLResultSet($result, false, $this->_dbConn->getSQLIdentifierCase());
            return true;
        }
    }

    /**
     * Gets all queries in the batch
     *
     * @return array
     */
    public function getBatchQueries()
    {
        return $this->_batchQueries;
    }

    /**
     * Moves to this SpicaStatement object's next result, deals with any current SpicaMySQLResultSet object(s)
     * according to the instructions specified by the given flag, and returns true if the next result
     * is a SpicaMySQLResultSet object
     *
     * There are no more results when the following is true:
     * @example
     *   // $stmt is a Statement object
     *   (($stmt->getMoreResults() == false) && ($stmt->getUpdateCount() == false))
     *   while ($stmt->getMoreResults()) {
     *       $rs = $stmt->getResultSet();
     *   }
     *
     * @param  int $current
     * @return bool true if there is one more result set or false if not
     */
    public function getMoreResults($current = SpicaStatement::CLOSE_CURRENT_RESULT)
    {
        switch ($current)
        {
            case SpicaStatement::CLOSE_CURRENT_RESULT:

                if (true === is_object($this->_currentResultSet))
                {
                    $this->_currentResultSet->close();
                }

                break;

            case SpicaStatement::CLOSE_ALL_RESULTS:

                if (true === is_object($this->_currentResultSet))
                {
                    $this->_currentResultSet->close();
                }

                $this->_closeAllOpenResults();

                break;

            case SpicaStatement::KEEP_CURRENT_RESULT:
                break;
        }

        $this->_currentResultSet = $this->getNextResultSet();
        // $this->_currentResultSet is null or an instance of MySQLResultSet
        return (null !== $this->_currentResultSet) ? true: false;
    }

    /**
     * Gets next result set in the batch
     *
     * @return SpicaMySQLResultSet|null
     */
    public function getNextResultSet()
    {
        $offset = ++$this->_previousBatchResultOffset;
        return isset($this->_batchResults[$offset])?$this->_batchResults[$offset]:null;
    }

    /**
     * Retrieves the current result as a SpicaMySQLResultSet object
     *
     * @return SpicaMySQLResultSet|null
     */
    public function getResultSet()
    {
        return $this->_currentResultSet;
    }

    /**
     * Gets the affected rows count after update query is excuted
     *
     * @return int
     */
    public function getUpdateCount()
    {
        if (false === $this->_isSelect && null !== $this->_currentResultSet &&
            true === $this->_currentResultSet->isAvailable())
        {
            try
            {
                $conn = $this->_dbConn->getNativeConnection(); // establish connection
            }
            catch (SpicaDatabaseException $ex)
            {
                throw new SpicaDatabaseException('Unable to retrieve number of rows that were affected by preceding SQL statement because database connection has been closed. ', null, null, null, $ex);
            }

            return mysqli_affected_rows($conn);
        }

        return false;
    }

    /**
     * Returns the last insert id
     *
     * This method returns the ID generated by a query on a table with a column having the AUTO_INCREMENT attribute
     *
     * When running extended inserts on a table with an AUTO_INCREMENT field,
     * the value of mysqli_insert_id() will equal the value of the *first* row inserted,
     * not the last, as you might expect
     *
     * @throws SpicaDatabaseException
     * @param  $table   The name of the table you inserted into.
     * @param  $field   The name of the autoincrement field
     * @return int|null Value of the AUTOINCREMENT column for the last INSERT
     */
    public function lastInsertId($table, $field)
    {
        // Check closed statement
        $this->_checkClosed();

        // More verbose and procedural way for performance and flexibility
        // We can use self::fetchOne() here with a little bit more overhead and less friendly error message
        $this->_lastQuery = 'SELECT LAST_INSERT_ID()';

        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to retrieve last insert ID because database connection has been closed. ', null, null, null, $ex);
        }

        $result   = mysqli_query($conn, 'SELECT LAST_INSERT_ID()');

        if (false === $result)
        {
            throw new SpicaDatabaseException('Could not retrieve last insert ID. ',
            mysqli_error($conn), mysqli_sqlstate($conn), mysqli_errno($conn));
        }

        // Possibly you did this query on a table that does not have any AUTOINCREMENT column
        $id = null;

        if (mysqli_num_rows($result) > 0)
        {
            // The mysqli_fetch_row function has an optional second parameter $row
            // but that can't be used for compatibility with Oracle, DB2, etc.
            $array = mysqli_fetch_row($result);
            $id    = $array[0];
        }

        mysqli_free_result($result);
        return (int) $id;
    }

    /**
     * Returns the connection ID (thread ID) for the connection
     *
     * Every connection has an ID that is unique among the set of currently connected clients.
     *
     * @see    SpicaStatement#getConnectionId()
     * @return int|null
     */
    public function getConnectionId()
    {
        // Check closed statement
        $this->_checkClosed();

        // More verbose and procedural way for performance and flexibility
        // We can use self::fetchOne() here with a little bit more overhead and less friendly error message
        $this->_lastQuery = 'SELECT CONNECTION_ID()';

        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to retrieve connection ID because database connection has been closed. ', null, null, null, $ex);
        }

        $result   = mysqli_query($conn, 'SELECT CONNECTION_ID()');

        if (false === $result)
        {
            throw new SpicaDatabaseException('Could not retrieve connection id. ', mysqli_error($conn), mysqli_sqlstate($conn), mysqli_errno($conn));
        }

        $id = null;

        if (mysqli_num_rows($result) > 0)
        {
            // The mysqli_fetch_row function has an optional second parameter $row
            // but that can't be used for compatibility with Oracle, DB2, etc.
            $array = mysqli_fetch_row($result);
            $id    = $array[0];
        }

        mysqli_free_result($result);
        return (int) $id;
    }

    /**
     * Returns the number of rows updated, inserted, or deleted by the preceding statement
     *
     * This is the same as the row count that the mysql client displays and the value
     * from the mysqli_affected_rows() C API function.
     *
     * ROW_COUNT() was added in MySQL 5.0.1.
     *
     * @return int
     */
    public function getAffectedRowCount()
    {
        // Check closed statement
        $this->_checkClosed();

        // More verbose and procedural way for performance and flexibility
        // We can use self::fetchOne() here with a little bit more overhead and less friendly error message
        $this->_lastQuery = 'SELECT ROW_COUNT()';

        // For SELECT, SHOW, DESCRIBE or EXPLAIN mysqli_query() will return a result object.
        // Otherwise, it returns TRUE on success or FALSE on failure
        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to retrieve affected row numbers because database connection has been closed. ', null, null, null, $ex);
        }

        $result   = mysqli_query($conn, 'SELECT ROW_COUNT()');

        if (false === $result)
        {
            throw new SpicaDatabaseException('Could not retrieve the number of affected rows. ', mysqli_error($conn), mysqli_sqlstate($conn), mysqli_errno($conn));
        }

        $id = null;

        if (mysqli_num_rows($result) > 0)
        {
            // The mysqli_fetch_row function has an optional second parameter $row
            // but that can't be used for compatibility with Oracle, DB2, etc.
            $array = mysqli_fetch_row($result);
            $id    = $array[0];
        }

        mysqli_free_result($result);
        return (int) $id;
    }

    /**
     * Fetches a single value of the first row and first column
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $sql
     * @return string|null Null value is returned when no row is found
     */
    public function fetchOne($sql)
    {
        // Check closed statement
        $this->_checkClosed();

        $this->_lastQuery = $sql;

        // For SELECT, SHOW, DESCRIBE or EXPLAIN mysqli_query() will return a result object.
        // Otherwise, it returns TRUE on success or FALSE on failure
        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL command to retrieve a single value because database connection has been closed. ', null, null, null, $ex);
        }

        $result   = mysqli_query($conn, $sql);

        if (false === $result)
        {
            throw new SpicaDatabaseException('Could not execute a query to retrieve a single value in the expected result set. ',
            mysqli_error($conn), mysqli_sqlstate($conn), mysqli_errno($conn));
        }

        $value = null;

        if (mysqli_num_rows($result) > 0)
        {
            // The mysqli_fetch_row function has an optional second parameter $row
            // but that can't be used for compatibility with Oracle, DB2, etc.
            $array = mysqli_fetch_row($result);
            $value = $array[0];
        }

        mysqli_free_result($result);
        return $value;
    }

    /**
     * Fetches a single column from a result set as an associative array
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $sql
     * @return array An empty array is returned when there is no row found
     */
    public function fetchColumn($sql)
    {
        // Check closed statement
        $this->_checkClosed();

        $this->_lastQuery = $sql;

        // For SELECT, SHOW, DESCRIBE or EXPLAIN mysqli_query() will return a result object.
        // Otherwise, it returns TRUE on success or FALSE on failure
        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL command to retrieve a single column result set because database connection has been closed. ', null, null, null, $ex);
        }

        $result   = mysqli_query($conn, $sql);

        if (false === $result)
        {
            throw new SpicaDatabaseException('Could not make a query to retrieve a single column result set. ', mysqli_error($conn), mysqli_sqlstate($conn), mysqli_errno($conn));
        }

        $values = array();

        while ($row = mysqli_fetch_row($result))
        {
            $values[] = $row[0];
        }

        mysqli_free_result($result);
        return $values;
    }

    /**
     * Fetches a single row from result set returned from SQL command as an associative array
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $sql
     * @param  int    $caseMode The value may be one of: null,
     *                          SpicaResultSet::IDENTIFIER_LOWERCASE,
     *                          SpicaResultSet::IDENTIFIER_UPPERCASE,
     *                          SpicaResultSet::IDENTIFIER_MIXEDCASE
     * @return array An empty array is returned when there is no row found
     */
    public function fetchRow($sql, $caseMode = null)
    {
        // Check closed statement
        $this->_checkClosed();

        $this->_lastQuery = $sql;

        // For SELECT, SHOW, DESCRIBE or EXPLAIN mysqli_query() will return a result object.
        // Otherwise, it returns TRUE on success or FALSE on failure
        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL command to retrieve a single row result set because database connection has been closed. ', null, null, null, $ex);
        }

        $result   = mysqli_query($conn, $sql);

        if (false === $result)
        {
            throw new SpicaDatabaseException('Could not execute SQL command to retrieve a single-row result set. ', mysqli_error($conn), mysqli_sqlstate($conn), mysqli_errno($conn));
        }

        // Returns an associative array that corresponds to the fetched row or NULL if there are no more rows.
        $values   = mysqli_fetch_assoc($result);

        if (null !== $values)
        {
            // Make it inline for performance (see switch statement below)
            if (null === $caseMode)
            {
                $caseMode  = $this->_dbConn->getSQLIdentifierCase();
            }

            // Same logic as in executeBatch()
            // Intentional duplicate code for performance
            $lowerCaseMode = SpicaResultSet::IDENTIFIER_LOWERCASE;
            $upperCaseMode = SpicaResultSet::IDENTIFIER_UPPERCASE;

            switch ($caseMode)
            {
                case $lowerCaseMode:
                    $values = array_change_key_case($values, CASE_LOWER);
                    break;

                case $upperCaseMode:
                    $values = array_change_key_case($values, CASE_UPPER);
                    break;

                default:
                // no-op (present here for coding standard compliant)
            }
        }
        else
        {
            $values = array();
        }

        mysqli_free_result($result);
        return $values; // Always returns an array (An empty array means there is no row is found with the SQL command)
    }

    /**
     * Fetches result set of multiple rows returned from SQL command as an associative array
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $sql
     * @param  int    $caseMode The value may be one of: null,
     *                          SpicaResultSet::IDENTIFIER_LOWERCASE,
     *                          SpicaResultSet::IDENTIFIER_UPPERCASE,
     *                          SpicaResultSet::IDENTIFIER_MIXEDCASE
     * @return array An empty array is returned when there is no row found
     */
    public function fetchAll($sql, $caseMode = null)
    {
        // Check closed statement
        $this->_checkClosed();

        $this->_lastQuery = $sql;

        // For SELECT, SHOW, DESCRIBE or EXPLAIN mysqli_query() will return a result object.
        // Otherwise, it returns TRUE on success or FALSE on failure
        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL command to retrieve multiple-rows result set because database connection has been closed. ', null, null, null, $ex);
        }

        $result   = mysqli_query($conn, $sql);

        if (false === $result)
        {
            throw new SpicaDatabaseException('Could not execute SQL command to retrieve multiple-rows result set. ', mysqli_error($conn), mysqli_sqlstate($conn), mysqli_errno($conn));
        }

        $values  = array();
        // Make it inline for performance (see switch statement below)
        if (null === $caseMode)
        {
            $caseMode  = $this->_dbConn->getSQLIdentifierCase();
        }

        // Same logic as in executeBatch()
        // Intentional duplicate code for performance
        $lowerCaseMode = SpicaResultSet::IDENTIFIER_LOWERCASE;
        $upperCaseMode = SpicaResultSet::IDENTIFIER_UPPERCASE;

        // Field names returned by this function are case-sensitive
        while ($row    = mysqli_fetch_assoc($result))
        {
            switch ($caseMode)
            {
                case $lowerCaseMode:
                    $row = array_change_key_case($row, CASE_LOWER);
                    break;

                case $upperCaseMode:
                    $row = array_change_key_case($row, CASE_UPPER);
                    break;

                default:
                // no-op (present here for coding standard compliant)
            }

            $values[] = $row;
        }

        mysqli_free_result($result);
        return $values;
    }

    /**
     * Retrieves the certain number of rows counted on a given row sequence number.
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $sql  The SQL command
     * @param  int    $from The beginning row sequence number that will be retrieved, starting with 0
     * @param  int    $to   The last row sequence number that will be retrieved
     * @param  int    $caseMode The value may be one of: null,
     *                          SpicaResultSet::IDENTIFIER_LOWERCASE,
     *                          SpicaResultSet::IDENTIFIER_UPPERCASE,
     *                          SpicaResultSet::IDENTIFIER_MIXEDCASE
     * @return array
     */
    public function fetchRange($sql, $from = 0, $to = 20, $caseMode = null)
    {
        if ($from > $to)
        {
            throw new SpicaDatabaseException('The higher sequence row number ($from:'.htmlentities($from).') can not greater than
            the lower sequence row number ($to:'.htmlentities($to).').');
        }

        // Row at index $to is included
        $q = SpicaMySQLCommand::modifyLimitQuery($sql, $to - $from + 1, $from);
        return $this->fetchAll($q, $caseMode);
    }

    /**
     * Retrieves certain set of rows for page navigation.
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $sql           The SQL command
     * @param  int    $pageNumber    The page sequence number
     * @param  int    $recordPerPage The number of records that will be displayed on each page
     * @param  int    $caseMode The value may be one of: null,
     *                          ResultSet::IDENTIFIER_LOWERCASE,
     *                          ResultSet::IDENTIFIER_UPPERCASE,
     *                          ResultSet::IDENTIFIER_MIXEDCASE
     * @return array
     */
    public function fetchPage($sql, $pageNumber = 1, $recordPerPage = 20, $caseMode = null)
    {
        // Calculate the ceiling limit and floor limit
        // Ceiling limit (offset): Starting index of records to fetch from
        $offset           = ($pageNumber - 1)*$recordPerPage;
        $q = SpicaMySQLCommand::modifyLimitQuery($sql, $recordPerPage, $offset);
        return $this->fetchAll($q, $caseMode);
    }

    /**
     * Fetches random record(s) from a table
     * FIXME
     * @see    http://akinas.com/pages/en/blog/mysql_random_row/
     * @param  string $table
     * @param  string $columns
     * @return array 2-dimentional array
     */
    public function fetchRandom($table, $columns, $rowCount = 1)
    {
        // Check closed statement
        $this->_checkClosed();

        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL command to retrieve multiple-rows result set because database connection has been closed. ', null, null, null, $ex);
        }

        if (1 === $rowCount)
        {
            $result   = mysqli_query($conn, 'SELECT FLOOR(RAND() * COUNT(1)) AS offset FROM '.$table);

            if (false === $result)
            {
                throw new SpicaDatabaseException('Could not execute SQL command to retrieve multiple-rows result set. ', mysqli_error($conn), mysqli_sqlstate($conn), mysqli_errno($conn));
            }

            $offsetRow = mysql_fetch_object($result);
            return $this->fetchAll('SELECT * FROM '.$table.' LIMIT '.$offsetRow->offset.', 1');
        }
        /**
         (SELECT r1.id, r1.username, r1.seq_number
         FROM test2 AS r1 JOIN
         (SELECT CEIL(RAND() *
         (SELECT MAX(id)/COUNT(id)
         FROM test2)) AS id)
         AS r2
         WHERE r1.id >= r2.id
         ORDER BY r1.id ASC
         LIMIT 1)
         UNION

         (SELECT r1.id, r1.username, r1.seq_number
         FROM test2 AS r1 JOIN
         (SELECT CEIL(RAND() *
         (SELECT MAX(id)/COUNT(id)
         FROM test2)) AS id)
         AS r2
         WHERE r1.id >= r2.id
         ORDER BY r1.id ASC
         LIMIT 1)
         */

        $query = 'SELECT * FROM table ORDER BY RAND() LIMIT 0, '.$rowCount;
        return $this->fetchAll($query);
    }

    /**
     * This diagnostic method is to build up a HTML table to specify what is the optimizing strategy
     * used in executing a given SQL command
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $sql
     * @return string A HTML table
     */
    public function explain($sql)
    {
        if (true === $this->_isClosed)
        {
            throw new SpicaDatabaseException('Unable to execute the query because the query statement is closed. ');
        }

        $this->_lastQuery = $sql = 'EXPLAIN '.$sql;

        // For SELECT, SHOW, DESCRIBE or EXPLAIN mysqli_query() will return a result object.
        // Otherwise, it returns TRUE on success or FALSE on failure
        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL command to retrieve rows that represent query execution strategy applied to the current statement because database connection has been closed. ', null, null, null, $ex);
        }

        $result   = mysqli_query($conn, $sql);

        if (false === $result)
        {
            throw new SpicaDatabaseException('Could not execute SQL command to retrieve rows that represent query execution strategy applied to the current statement. ', mysqli_error($conn), mysqli_sqlstate($conn), mysqli_errno($conn));
        }

        // Firstly, we builds up table head
        $first       = true;
        $htmlTable   = '<table id="explainCmd"><thead><tr><td colspan="'.mysqli_num_fields($result).'">'.$sql.'</td></tr>';

        while ($row  = mysqli_fetch_assoc($result))
        {
            // Build table head
            if (true === $first)
            {
                $htmlTable .= '<tr>';

                foreach ($row as $field => $value)
                {
                    $htmlTable .= '<th>'.$field.'</th>';
                }

                $htmlTable .= '</tr>';
                // Table head finishes here
                $first  = false;
            }

            $htmlTable .= '</thead><tbody><tr>';

            // Build table rows
            foreach ($row as $field => $value)
            {
                $htmlTable .= '<td>'.$value.'</td>';
            }

            $htmlTable .= '</tr>';
        }

        $htmlTable .= '</tbody></table>';
        mysqli_free_result($result);
        return $htmlTable;
    }

    /**
     * Inserts a single or multiple records into a table, using an array as user input
     *
     * @throws SpicaDatabaseException when database connection is closed, read-only or SQL command execution fails
     * @param  string $tableName
     * @param  array  $data An array of user input with specification: [field => value]
     * @return int An integer greater than zero indicates the number of rows affected or retrieved
     */
    public function insert($tableName, $data)
    {
        // throws SpicaDatabaseException
        $this->_checkUpdatable();

        if (false === is_array($data) || 0 === count($data))
        {
            throw new SpicaDatabaseException('Unable to execute SQL\'s INSERT command because the second parameter to insert() method is not a non-empty array. ', null, null, null);
        }

        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL\'s INSERT command because database connection has been closed. ', null, null, null);
        }

        $values = array();

        foreach ($data as $field => $value)
        {
            $fields[] = $field ;

            if (null  === $value)
            {
                $values[] = 'NULL';
                continue;
            }

            if (true === is_object($value))
            {
                $values[] = (string)$value; // SpicaQueryExpression awareness
            }
            else
            {
                $values[] = '\'' . mysqli_real_escape_string($conn, $value) . '\'';
            }
        }

        $this->_lastQuery = 'INSERT INTO ' . $tableName . ' (' . implode(', ', $fields) . ') VALUES (' . implode(', ', $values) . ')';

        // Execute this query with SpicaMySQLStatement::execute() method is not neccessary
        // We don't need to make things complicated here
        $result = mysqli_query($conn, $this->_lastQuery);
        $code   = mysqli_errno($conn);

        if ($code > 0)
        {
            throw new SpicaDatabaseException('Unable to execute SQL\'s INSERT command. ', mysqli_error($conn), mysqli_sqlstate($conn), $code);
        }

        // Returns the number of rows affected by the last INSERT, UPDATE, REPLACE or DELETE query
        return mysqli_affected_rows($conn);
    }

    /**
     * Inserts multiple records into a table, using an array as user input
     *
     * @throws SpicaDatabaseException when database connection is closed, read-only or SQL command execution fails
     * @param  string $tableName
     * @param  array $data An array of user input with specification: Array(0 => array([field => value]), 1 => array([field => value]))
     * @param  array $onDuplicate Array [field => value] to build up an expression 'ON DUPLICATE KEY UPDATE'
     * @return int An integer greater than zero indicates the number of rows affected or retrieved
     */
    public function insertMultiple($tableName, $data, $onDuplicate = array())
    {
        // throws SpicaDatabaseException
        $this->_checkUpdatable();

        if (false === is_array($data) || 0 === count($data))
        {
            throw new SpicaDatabaseException('Unable to execute SQL\'s INSERT command because the second parameter to insert() method is not a non-empty array. ', null, null, null);
        }

        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL\'s INSERT command because database connection has been closed. ', null, null, null);
        }

        $this->_lastQuery = SpicaMySQLCommand::createBatchInsertStatement($this->_dbConn, $tableName, $data);

        if (false === empty($onDuplicate))
        {
            $pair = array();

            foreach ($onDuplicate as $field => $value)
            {
                if (true === is_object($value))
                {
                    $pair[] = '`' . $field . '` = (' . (string) $value . ')'; // SpicaQueryExpression awareness
                }
                else
                {
                    $pair[] = '`' . $field . '` = \'' . mysqli_real_escape_string($conn, $value) . '\'';
                }
            }

            $this->_lastQuery .= ' ON DUPLICATE KEY UPDATE '.implode(', ', $pair);
        }

        // Execute this query with SpicaMySQLStatement::execute() method is not neccessary
        // We don't need to make things complicated here
        $result = mysqli_query($conn, $this->_lastQuery);
        $code   = mysqli_errno($conn);

        if ($code > 0)
        {
            throw new SpicaDatabaseException('Unable to execute SQL\'s INSERT command. ', mysqli_error($conn), mysqli_sqlstate($conn), $code);
        }

        // Returns the number of rows affected by the last INSERT, UPDATE, REPLACE or DELETE query
        return mysqli_affected_rows($conn);
    }

    /**
     * Updates data in a table, using an array as user input
     *
     * @throws SpicaDatabaseException when database connection is closed, read-only or SQL command execution fails
     * @param  string       $tableName
     * @param  array        $data An array of user input with specification: [field => value]
     * @param  string|array $where Array of fields and its conditions (array('id' => '> 3')
     *                      or conditional phrase for UPDATE SQL command
     * @return int An integer greater than zero indicates the number of rows affected or retrieved.
     *             Zero indicates that no records where updated for an UPDATE statement, no rows matched the WHERE clause
     *             in the query or that no query has yet been executed
     */
    public function update($tableName, $data, $where = array())
    {
        // throws SpicaDatabaseException
        $this->_checkUpdatable();

        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL\'s UPDATE command because database connection has been closed. ', null, null, null);
        }

        foreach ($data as $field => $value)
        {
            if (null    === $value)
            {
                $pair[] = '`' . $field . '` = NULL';
                continue;
            }

            if (true === is_object($value))
            {
                $pair[] = '`' . $field . '` = (' . (string) $value . ')'; // SpicaQueryExpression awareness
            }
            else
            {
                $pair[] = '`' . $field . '` = \'' . mysqli_real_escape_string($conn, $value) . '\'';
            }
        }

        // Conditional phrase
        $where = SpicaMySQLCommand::buildWherePhrase($where, $this->_dbConn);

        if (false === $where)
        {
            throw new SpicaDatabaseException('Unable to execute SQL\'s UPDATE command because WHERE phrase is not valid', null, null, null);
        }

        $this->_lastQuery = 'UPDATE ' . $tableName . ' SET ' . implode(', ', $pair) . $where;

        // Execute this query with MySQLStatement::execute() method is not neccessary
        // We don't need to make things complicated here
        $result = mysqli_query($conn, $this->_lastQuery);

        $code   = mysqli_errno($conn);

        if ($code > 0)
        {
            throw new SpicaDatabaseException('Unable to execute SQL\'s UPDATE command. ', mysqli_error($conn), mysqli_sqlstate($conn), $code);
        }

        // Returns the number of rows affected by the last INSERT, UPDATE, REPLACE or DELETE query
        return mysqli_affected_rows($conn);
    }

    /**
     * Deletes a single row or multiple rows in a table, using an array as user input conditions
     *
     * @throws SpicaDatabaseException when database connection is closed, read-only or SQL command execution fails
     * @param  string $tableName
     * @param  array|string $where Conditional phrase for DELETE SQL command.
     *                      As an arrray: [field => condition]. E.x : array('id' => null, 'name' = 'LIKE %pcdinh%')
     *                      As a string: "id = 3 AND username = 'pcdinh'" (without WHERE)
     * @return int An integer greater than zero indicates the number of rows affected or retrieved.
     *             Zero indicates that no records where updated for an DELETE statement, no rows matched the WHERE clause
     *             in the query or that no query has yet been executed
     */
    public function delete($tableName, $where = array())
    {
        // throws SpicaDatabaseException
        $this->_checkUpdatable();

        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL\'s DELETE command because database connection has been closed. ', null, null, null);
        }

        // Conditional phrase
        $where = SpicaMySQLCommand::buildWherePhrase($where, $this->_dbConn);

        if (false === $where)
        {
            throw new SpicaDatabaseException('Unable to execute SQL\'s DELETE command because WHERE conditional phrase is not valid. ', null, null, null);
        }

        $this->_lastQuery = 'DELETE FROM ' . $tableName . $where;

        // Execute this query with MySQLStatement::execute() method is not neccessary
        // We don't need to make things complicated here
        $result = mysqli_query($conn, $this->_lastQuery);

        $code   = mysqli_errno($conn);

        if ($code > 0)
        {
            throw new SpicaDatabaseException('Unable to execute SQL\'s DELETE command. ', mysqli_error($conn), mysqli_sqlstate($conn), $code);
        }

        // Returns the number of rows affected by the last INSERT, UPDATE, REPLACE or DELETE query
        return mysqli_affected_rows($conn);
    }

    /**
     * Deletes an entire existing record or multiple existing records based on
     * an unique index (with single or multiple keys) or a primary key to replace it
     * with new one(s) or insert new records if there is no existing match(es)
     *
     * REPLACE works exactly like INSERT, except that if an old row in the table
     * has the same value as a new row for a PRIMARY KEY or a UNIQUE index,
     * the old row is deleted before the new row is inserted.
     *
     * Using REPLACE INTO to insert a record will keep you from inserting duplicate
     * records into your table.
     *
     * The REPLACE INTO command works in one of two ways:
     * + It inserts data into a specified record.
     * + If the data to be inserted violates key uniqueness, it deletes the existing record first and then
     * inserts the replacement record.
     *
     * REPLACE is a MySQL extension to the SQL standard. It either inserts, or deletes  and inserts
     *
     * @see    http://dev.mysql.com/doc/refman/5.1/en/replace.html
     * @throws SpicaDatabaseException when database connection is closed, read-only or SQL command execution fails
     * @param  string $tableName
     * @param  array  $data An array of user input with specification: [field => value]
     * @param  array  $options Optional arguments
     * @return int An integer greater than zero to indicate the number of rows affected. This is the sum of the rows deleted and inserted
     */
    public function replace($tableName, $data, $options = array())
    {
        $this->_checkUpdatable();

        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL\'s REPLACE command because database connection has been closed. ', null, null, null);
        }

        foreach ($data as $field => $value)
        {
            $fields[]     = '`' . $field . '`';

            if (null      === $value)
            {
                $values[] = 'NULL';
                continue;
            }

            $values[]     = '\'' . mysqli_real_escape_string($conn, $value) . '\'';
        }

        $this->_lastQuery = 'REPLACE INTO ' . $tableName . ' (' . implode(', ', $fields) . ') VALUES (' . implode(', ', $values) . ')';

        // Execute this query with MySQLStatement::execute() method is not neccessary
        // We don't need to make things complicated here
        $result = mysqli_query($conn, $this->_lastQuery);

        $code   = mysqli_errno($conn);

        if ($code > 0)
        {
            throw new SpicaDatabaseException('Unable to execute SQL\'s REPLACE command. ',
            mysqli_error($conn), mysqli_sqlstate($conn), $code);
        }

        // Returns the number of rows affected by the last INSERT, UPDATE, REPLACE or DELETE query
        return mysqli_affected_rows($conn);
    }

    /**
     * Inserts an entire existing record based on an unique index (with single or multiple keys)
     * or a primary key by new one(s) or update the current matching one(s).
     *
     * FIXME
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $tableName
     * @param  array  $data An array of user input with specification: [field => value]
     * @param  array  $options Optional arguments
     * @return int An integer greater than zero indicates the number of rows affected or retrieved.
     *             Zero indicates that no records where updated for an UPDATE statement, no rows matched the WHERE clause
     *             in the query or that no query has yet been executed
     */
    public function merge($tableName, $data, $options = array())
    {
        throw new SpicaDatabaseException('This feature has not been implemented yet. ');
    }

    public function getGeneratedKeys()
    {
        // FIXME
    }

    /**
     * Cleans results and queries stored in the current batch
     */
    public function clearBatch()
    {
        $this->_clearBatchResult();
        $this->_batchQueries = array();
    }

    /**
     * Closes the statement actually
     *
     * @param bool $calledExplicitly
     */
    protected function _realClose($calledExplicitly = false)
    {
        if (true === $this->_isClosed)
        {
            return true;
        }

        $this->_closeAllOpenResults();
        $this->_currentResultSet = null;
        $this->_isClosed         = true;

        return true;
    }

    /**
     * Frees resources stored in the current batch results
     *
     * @see SpicaMysqlStatement::clearBatch()
     */
    protected function _clearBatchResult($offset = 0)
    {
        $totalResult = count($this->_batchResults);

        if ($offset >= $totalResult)
        {
            throw new InvalidArgumentException('Unable to clean batch result numbered '.$offset.'. Its offset exceeds the total batch result available. ');
        }

        for ($i = $offset; $i < $totalResult; $i++)
        {
            if (true === is_resource($this->_batchResults[$i]))
            {
                mysqli_free_result($this->_batchResults[$i]);
            }
        }

        $this->_batchResults = array();
        $this->_previousBatchResultOffset = -1;
    }

    /**
     * Closes all open result set in the batch
     *
     * @see SpicaMySQLStatement::_realClose()
     */
    protected function _closeAllOpenResults()
    {
        if (null !== $this->_batchResults)
        {
            foreach ($this->_batchResults as $num => $rs)
            {
                if ($rs instanceof SpicaMySQLResultSet)
                {
                    $rs->close();
                }
            }
        }

        $this->_batchResults = null;
    }

    /**
     * Destroys the current <code>SpicaMySQLStatement</code> object
     */
    public function __destruct()
    {

    }
}

/**
 * A table of data representing a database result set, which is usually generated by
 * executing a statement that queries the database
 *
 * @category   spica
 * @package    core
 * @subpackage datasource\db\mysql
 * @author     Pham Cong Dinh <pcdinh at phpvietnam dot net>
 * @since      Version 0.1
 * @since      October 18, 2008
 * @copyright  Pham Cong Dinh (http://www.phpvietnam.net)
 * @license    http://www.gnu.org/licenses/lgpl-3.0.txt
 */
class SpicaMySQLResultSet implements SpicaResultSet
{
    /**
     * Native MySQL resource set object
     *
     * @var mysqli_result
     */
    protected $_result;

    /**
     * Is this result available to retrieve from?
     *
     * If it is not, this result set contains an error and carry a message to explain this status
     *
     * @var bool
     */
    protected $_isAvailable = false;

    /**
     * A flag to indicate if the result set is closed
     *
     * @var bool
     */
    protected $_isClosed = false;

    /**
     * The current row that the cursor points to
     *
     * @var int
     */
    protected $_currentCursor = 0;

    /**
     * The total rows in the result set
     *
     * @var int
     */
    protected $_rowCount;

    /**
     * The total fields in the result set
     *
     * @var int
     */
    protected $_fieldCount;

    /**
     * An associative array that corresponds to the currently fetched row or NULL if there are no more rows
     *
     * @var array|null
     */
    protected $_currentRow;

    /**
     * Is this result set be produced by a SELECT or SHOW statement?
     *
     * @var bool
     */
    protected $_isSelect = true;

    /**
     * All fields names should be in lowercase, upper case or mixed case
     *
     * @var int
     */
    protected $_fieldCase;

    /**
     * Constructs an object of <code>SpicaMySQLResultSet</code>
     *
     * The parameter $result can be an instance of mysqli_result if the statement is SELECT, SHOW, EXPLAIN
     * query or be a FALSE value if the statement contains something wrong or be a TRUE value
     * if the statement is INSERT, UPDATE, DELETE, REPLACE query
     *
     * @param mysqli_result|bool MySQL mysqli_result object or boolean value
     * @param bool $readQuery
     * @param int  $fieldCase
     */
    public function __construct($result, $readQuery, $fieldCase)
    {
        if ($result instanceof mysqli_result)
        {
            $this->_result      = $result;
            $this->_isAvailable = true;
        }
        else
        {
            $this->_isAvailable = (bool) $result;
        }

        $this->_fieldCase = $fieldCase;
        $this->_isSelect  = $readQuery;
    }

    /**
     * Retrieves all rows in the current result set as a <code>SpicaRowList</code> object
     *
     * @return SpicaRowList
     */
    public function getRowList()
    {
        include_once 'library/spica/core/utils/ContainerUtils.php';
        return new SpicaRowList($this->getAssociativeArray());
    }

    /**
     * Retrieves XML representation of all rows in the current result set
     *
     * @return string
     */
    public function getXml()
    {
        include_once 'library/spica/core/utils/FormatUtils.php';
        return SpicaFormatUtils::arrayToXml($this->getAssociativeArray());
    }

    /**
     * Gets a JSON representation of rows contained in the result set
     *
     * @return string
     */
    public function getJson()
    {
        return json_encode($this->getAssociativeArray());
    }

    /**
     * Fetches all rows from the result set as an associative array
     *
     * This method always returns an array. Field names returned by this function are case-sensitive
     *
     * @return array
     */
    public function getAssociativeArray()
    {
        $this->_checkAvailability();

        if (null === $this->_result)
        {
            throw new BadMethodCallException('Method getAssociativeArray() requires SELECT statement. ');
        }

        $rs = array();

        // Makes it local for performance
        if ($this->_fieldCase === SpicaResultSet::IDENTIFIER_LOWERCASE)
        {
            // Looping through the resultset
            while ($row = mysqli_fetch_assoc($this->_result))
            {
                $rs[]   = array_change_key_case($row, CASE_LOWER);
            }
        }
        else
        {
            // Looping through the resultset
            while ($row = mysqli_fetch_assoc($this->_result))
            {
                $rs[]   = $row;
            }
        }

        return $rs;
    }

    /**
     * Determines how many result rows were found by the preceding query
     *
     * @return int The number of result rows.
     */
    public function rowCount()
    {
        // Verbose code for performance
        if (null !== $this->_rowCount)
        {
            return $this->_rowCount;
        }

        $this->_checkAvailability();

        if ($this->_result instanceof mysqli_result)
        {
            $this->_rowCount = mysqli_num_rows($this->_result);
        }

        return $this->_rowCount;
    }

    /**
     * Moves the cursor to the given row number in this <code>SpicaResultSet</code> object
     *
     * @param  int $row
     * @return bool
     */
    public function absolute($row)
    {
        if (false === is_int($row))
        {
            throw new InvalidArgumentException('Method absolute() requires its first parameter to be an integer. ');
        }

        // http://bugs.mysql.com/bug.php?id=38252
        if (0 === $row)
        {
            $row = 1;
        }

        try
        {
            $this->_checkAvailability();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to move the cursor the offset '.$row.' because the result set is not available or closed', null, null, null, $ex);
        }

        if ($row > $this->rowCount())
        {
            return false;
        }

        mysqli_data_seek($this->_result, $row - 1);

        $rs = mysqli_fetch_assoc($this->_result);

        if (null === $rs)
        {
            return false;
        }

        if ($this->_fieldCase === SpicaResultSet::IDENTIFIER_LOWERCASE)
        {
            $rs = array_change_key_case($rs, CASE_LOWER);
        }

        $this->_currentCursor = $row;
        $this->_currentRow    = $rs;
        return true;
    }

    /**
     * Moves the cursor to the first row in this <code>SpicaResultSet</code> object
     *
     * @return bool
     */
    public function first()
    {
        return $this->absolute(1);
    }

    /**
     * Retrieves the current row number
     *
     * The first row is number 1, the second number 2, and so on.
     *
     * @see    SpicaResultSet#currentRowNumber
     * @return int
     */
    public function currentRowNumber()
    {
        return $this->_currentCursor;
    }

    /**
     * Returns row at the current cursor
     *
     * @see    SpicaResultSet#absolute()
     * @return array
     */
    public function currentRow()
    {
        return $this->_currentRow;
    }

    /**
     * Tests if it is possible to retrieve data from a <code>SpicaResultSet</code> object
     *
     * A <code>SpicaResultSet</code> object is not available to extract data
     * when this object contains an error
     *
     * @return bool
     */
    public function isAvailable()
    {
        return $this->_isAvailable;
    }

    /**
     * Retrieves whether this <code>SpicaResultSet</code> object has been closed
     *
     * A <code>SpicaStatement</code> is closed if the method close has been called on it,
     * or if it is automatically closed
     *
     * @return bool true if this <code>SpicaStatement</code> object is closed;
     *              false if it is still open
     */
    public function isClosed()
    {
        return $this->_isClosed;
    }

    /**
     * Retrieves whether the cursor is on the first row of this
     * <code>SpicaResultSet</code> object
     *
     * @return bool
     */
    public function isFirst()
    {
        return (1 === $this->_currentCursor);
    }

    /**
     * Retrieves whether the cursor is on the last row of this <code>SpicaResultSet</code> object
     *
     * @return bool
     */
    public function isLast()
    {
        return ($this->rowCount() === $this->_currentCursor);
    }

    /**
     * Moves the cursor to the last row in this <code>SpicaResultSet</code> object
     *
     * @return bool
     */
    public function last()
    {
        $this->_currentCursor = $this->_rowCount;
        return $this->absolute(($this->_rowCount));
    }

    /**
     * Moves the cursor to the previous row in this <code>SpicaResultSet</code> object
     *
     * @return bool
     */
    public function previous()
    {
        if (0 >= $this->_currentCursor)
        {
            return false;
        }

        $cursor = $this->_currentCursor - 1;

        if (0 > $cursor)
        {
            return false;
        }

        return $this->absolute($cursor);
    }

    /**
     * Moves the cursor forward one row from its current position
     *
     * @return bool
     */
    public function next()
    {
        $this->_checkAvailability();
        if (0 === $this->rowCount())
        {
            return false;
        }

        if ($this->_rowCount < $this->_currentCursor + 1)
        {
            return false;
        }

        // Field names returned by this function are case-sensitive
        $this->_currentRow = mysqli_fetch_assoc($this->_result);

        if (null === $this->_currentRow)
        {
            return false;
        }

        $this->_currentCursor++;
        return true;
    }

    /**
     * This method returns data about the columns returned as part of the
     * result set as a <code>SpicaResultSetMetaData</code> instance.
     *
     * @exception SpicaDatabaseException If an error occurs.
     * @return SpicaResultSetMetaData The <code>SpicaResultSetMetaData</code> instance for this result set.
     */
    public function getMetaData()
    {
        $this->_checkAvailability();
        return new SpicaMySQLResultSetMetaData($this->_result, $this->_isSelect, $this->_fieldCase);
    }

    /**
     * Releases this <code>SpicaResultSet</code> object's database
     * and resources immediately instead of waiting for this to happen when it is automatically closed
     */
    public function close()
    {
        $this->_realClose(true);
    }

    /**
     * Releases this <code>SpicaResultSet</code> object's database
     *
     * @param bool $calledExplicitly
     */
    protected function _realClose($calledExplicitly = false)
    {
        if (true === $this->_isAvailable && true === $this->_isSelect)
        {
            mysqli_free_result($this->_result);
        }

        $this->_currentCursor = 0;
        $this->_currentRow    = null;
        $this->_rowCount      = null;
        $this->_fieldCount    = null;
        $this->_isClosed      = true;
        $this->_result        = null;
        $this->_isAvailable   = false;
    }

    /**
     * Tests the availability of the result set to extract data from.
     *
     * @throws SpicaDatabaseException
     */
    protected function _checkAvailability()
    {
        if (true === $this->_isClosed)
        {
            throw new SpicaDatabaseException('Unable to extract data from a closed result set. ');
        }

        if (false === $this->_isAvailable)
        {
            throw new SpicaDatabaseException('Unable to extract data from a result set that is produced by an errornous statement. ');
        }
    }

    /**
     * Destroys the current <code>SpicaMySQLResultSet</code>
     */
    public function __destruct()
    {
        if (true === $this->_isAvailable && true === $this->_isSelect)
        {
            mysqli_free_result($this->_result);
        }
    }
}

/**
 * The SpicaResultSetMetaData interface creates an object that can be used to find out
 * about the types and properties of the columns in a SpicaResultSet.
 *
 * @category   spica
 * @package    core
 * @subpackage datasource
 * @author     Pham Cong Dinh <pcdinh at phpvietnam dot net>
 * @since      Version 0.3
 * @since      December 12, 2008
 * @copyright  Pham Cong Dinh (http://www.phpvietnam.net)
 * @license    http://www.gnu.org/licenses/lgpl-3.0.txt
 * @version    $Id: Statement.php 1867 2010-06-07 04:40:51Z pcdinh $
 */
class SpicaMySQLResultSetMetaData implements SpicaResultSetMetaData
{
    /**
     * Is this result set be produced by a SELECT or SHOW statement?
     *
     * @var bool
     */
    protected $_isSelect = true;

    /**
     * All fields names should be in lowercase, upper case or mixed case
     *
     * @var int
     */
    protected $_fieldCase;

    /**
     * The total fields in the result set
     *
     * @var int
     */
    protected $_fieldCount;

    /**
     * Names of columns
     *
     * @var array
     */
    protected $_columns;

    /**
     * Information on fields
     *
     * @var an array of stdClass
     */
    protected $_columnInfo;

    /**
     * Is column names populated?
     *
     * @var bool
     */
    protected $_columnPopulated = false;

    /**
     * Native MySQL resource set object
     *
     * @var mysqli_result
     */
    protected $_result;

    /**
     * Constructs an object of <code>SpicaMySQLResultSetMetaData</code>
     *
     * The parameter $result can be an instance of mysqli_result if the statement is SELECT, SHOW, EXPLAIN
     * query or be a FALSE value if the statement contains something wrong or be a TRUE value
     * if the statement is INSERT, UPDATE, DELETE, REPLACE query
     *
     * @param mysqli_result|bool MySQL mysqli_result object or boolean value
     * @param bool $readQuery
     * @param int  $fieldCase
     */
    public function __construct($result, $readQuery, $fieldCase)
    {
        if ($result instanceof mysqli_result)
        {
            $this->_result = $result;
        }

        $this->_fieldCase = $fieldCase;
        $this->_isSelect  = $readQuery;
    }

    /**
     * This method returns the number of columns in the result set.
     *
     * @exception SpicaDatabaseException If an error occurs.
     * @return int|false The number of columns in the result set.
     */
    public function getColumnCount()
    {
        if (null === $this->_result)
        {
            return false;
        }

        // Verbose code for performance
        if (null !== $this->_fieldCount)
        {
            return $this->_fieldCount;
        }

        $this->_fieldCount = mysqli_num_fields($this->_result);
        return $this->_fieldCount;
    }

    /**
     * This method returns the name of the specified column.
     *
     * @exception SpicaDatabaseException If an error occurs.
     * @param  int $column The index of the column to return the name of.
     * @return string|false The name of the column.
     */
    public function getColumnName($column)
    {
        if (null === $this->_result)
        {
            return false;
        }

        if (false === $this->_columnPopulated)
        {
            $this->_populateColumnNames();
        }

        if (null !== $this->_columns && isset($this->_columns[$column]))
        {
            return $this->_columns[$column];
        }

        return false;
    }

    /**
     * This method returns names of columns by order.
     *
     * @exception SpicaDatabaseException If an error occurs.
     * @return array|false The ordered names of columns.
     */
    public function getColumnNames()
    {
        if (null === $this->_result)
        {
            return false;
        }

        if (false === $this->_columnPopulated)
        {
            $this->_populateColumnNames();
        }

        if (null !== $this->_columns)
        {
            return $this->_columns;
        }

        return false;
    }

    /**
     * This method returns the name of the schema that contains the specified
     * column.
     *
     * @exception SpicaDatabaseException If an error occurs.
     * @param  int $column The index of the column to check the schema name for.
     * @return string The name of the schema that contains the column.
     */
    public function getSchemaName($column)
    {
        return $this->getTableName($column);
    }

    /**
     * This method returns the name of the table containing the specified
     * column.
     *
     * @exception SpicaDatabaseException If an error occurs.
     * @param  index The index of the column to check the table name for.
     * @return string|false The name of the table containing the column.
     */
    public function getTableName($column)
    {
        if (null === $this->_result)
        {
            return false;
        }

        if (null === $this->_columnInfo)
        {
            // Get field information for all columns
            $this->_columnInfo = $this->_result->fetch_fields();
        }

        if (true === isset($this->_columnInfo[0]))
        {
            return $this->_columnInfo[0]->orgtable;
        }

        return false;
    }

    /**
     * Fetches data from native object and populate data into #_columnInfo
     */
    protected function _populateColumnNames()
    {
        if (null === $this->_columnInfo)
        {
            // Get field information for all columns
            $this->_columnInfo = $this->_result->fetch_fields();
        }

        $cols = array();
        foreach ($this->_columnInfo as $info)
        {
            $cols[] = $info->name;
        }

        $this->_columns = $cols;
        $this->_columnPopulated = true;
    }
}

?>